S09-03 MySQL-高级SQL查询

[TOC]

DQL

基础查询

~~DQL（Data Query Language，数据查询语言）~~ 用于从数据库表中检索数据，是 SQL 中使用频率最高、语法结构最灵活的部分。其核心关键字为 SELECT，执行过程仅读取数据，不会改变数据库的物理状态。

语法结构

DQL 的标准编写顺序遵循固定的语法骨架：

sql

SELECT [DISTINCT]
  字段列表 / 聚合函数 / 表达式 [AS 别名]
FROM
  表名 [AS 别名]
[JOIN 连接表 ON 连接条件]
[WHERE 条件列表]
[GROUP BY 分组字段列表 [HAVING 分组后过滤条件]]
[ORDER BY 排序字段 [ASC | DESC]]
[LIMIT 起始索引, 查询记录数];

执行顺序

SQL 的书写顺序与底层的逻辑执行顺序不同。理解执行顺序是编写高效查询和避免语法错误的关键。

DQL 逻辑执行的完整阶段：

FROM / JOIN：锁定数据源表，多表时计算笛卡尔积并基于 ON 过滤生成虚拟表。
WHERE：对基表或连接后的数据进行单行级别的初步条件过滤。
GROUP BY：将过滤后的行数据按照指定列进行聚合分组。
HAVING：对分组聚合后的统计结果进行二次过滤。
SELECT：提取目标列，执行表达式计算及别名赋予。
DISTINCT：对最终投影出的数据行执行去重操作。
ORDER BY：根据指定字段及排序规则（升序/降序）对结果集排序。
LIMIT：截取指定区间的数据行并输出给客户端。

WHERE

WHERE 子句是 DQL 中用于进行行级别数据过滤的核心条件子句。它根据指定的布尔表达式对数据表中的原始记录进行逐行评估，仅保留评估结果为 TRUE 的行。

~~基本语法~~：

WHERE 子句紧随 FROM 之后，语法结构如下：

sql

SELECT 字段列表
FROM 表名
WHERE 条件表达式;

~~执行机制~~：

在 SQL 的逻辑执行生命周期中，WHERE 的过滤步骤如下：

定位数据源：执行 FROM 子句加载表数据或完成多表连接。
逐行评估：遍历数据行，将字段值代入 WHERE 后的条件表达式中计算。
过滤记录：
- 结果为 TRUE：保留该行数据进入下一处理阶段。
- 结果为 FALSE 或 UNKNOWN（如与 NULL 进行常规比较）：丢弃该行。
传递中间集：将过滤后的数据集交付给后续的 GROUP BY 或 SELECT 进行投影计算。

~~运算符分类~~：

WHERE 支持多种条件运算符，可根据业务场景灵活组合：

类别	运算符	示例	说明
比较运算	`=`, `!=` / `<>`, `>`, `<`, `>=`, `<=`	`age >= 18`	基础数值、日期或字符大小比较
范围区间	`BETWEEN ... AND ...`	`salary BETWEEN 5000 AND 10000`	闭区间范围匹配（包含边界值）
集合匹配	`IN (...)` / `NOT IN (...)`	`dept_id IN (101, 102, 103)`	匹配列表中的任意一个离散值
模糊匹配	`LIKE` / `NOT LIKE`	`name LIKE '张%'`	`%` 匹配任意字符，`_` 匹配单个字符
空值判断	`IS NULL` / `IS NOT NULL`	`email IS NULL`	专用空值校验语法
逻辑运算	`AND`, `OR`, `NOT`	`status = 1 AND age > 20`	组合多个条件（`AND` 优先级高于 `OR`）

~~注意事项~~：

禁止直接使用聚合函数：WHERE 针对的是单行原始记录，无法使用 COUNT()、AVG()、SUM() 等分组聚合函数；若需对聚合结果过滤，应使用 HAVING。
不能使用 SELECT 中的别名：由于 WHERE 在 SELECT 之前执行，此时别名尚未生成，在 WHERE 中引用列别名会导致语法错误。
避免在索引列上使用函数：在 WHERE 条件中对字段进行函数转换（如 WHERE YEAR(create_time) = 2026）会导致索引失效，引发全表扫描。
三值逻辑与 NULL：与 NULL 进行常规比较（如 col = NULL）结果为 UNKNOWN，必须使用 IS NULL 或安全等于运算符 <=>。

GROUP BY

GROUP BY 子句用于将查询结果集按照一个或多个列的值进行逻辑分组。它通常配合聚合函数使用，实现对各个分组数据的统计、汇总与计算。

~~基本语法~~：

GROUP BY 位于 WHERE 之后、ORDER BY 之前：

sql

SELECT 分组字段列表, 聚合函数(计算字段)
FROM 表名
[WHERE 行级过滤条件]
GROUP BY 分组字段列表
[HAVING 组级过滤条件]
[ORDER BY 排序字段];

~~执行机制~~：

MySQL 在执行包含 GROUP BY 的查询时，底层主要经历以下阶段：

行级初筛：执行 WHERE 子句过滤出符合条件的原始记录。
数据分组：
- 走索引：若分组字段已建立索引，直接利用索引的有序性进行顺序扫描分组。
- 不走索引：构建内存临时表（Memory 引擎）存放中间结果；数据量过大时溢出到磁盘临时表。
聚合计算：对每个组内的数据行执行指定的聚合函数（如求和、计数）。
组级筛选：执行 HAVING 子句，基于聚合计算后的统计指标剔除不满足条件的组。
投影输出：根据 SELECT 提取最终的分组列与聚合指标。

~~常见用法~~：

1. 聚合函数配合

分组的核心价值在于结合聚合函数对组内数据进行汇总运算：

聚合函数	作用	示例
`COUNT()`	统计组内记录数	`COUNT(*)` 统计每组总人数
`SUM()`	计算数值字段总和	`SUM(salary)` 统计部门总薪资
`AVG()`	计算数值字段平均值	`AVG(score)` 统计班级平均分
`MAX()` / `MIN()`	提取组内最大 / 最小值	`MAX(price)` 获取各类目最高价

sql

-- 统计各部门的员工数、最高薪资与平均薪资
SELECT
  department_id,
  COUNT(*) AS total_staff,
  MAX(salary) AS highest_salary,
  AVG(salary) AS avg_salary
FROM employees
WHERE status = 'ACTIVE'
GROUP BY department_id;

2. 多字段分组

支持传入多个字段进行多维细分，系统会按照字段从左到右的层级组合进行交叉分组：

sql

-- 按年份与月份进行双重维度统计销售额
SELECT year, month, SUM(amount) AS total_sales
FROM sales_records
GROUP BY year, month;

3. 汇总增强

在 GROUP BY 后添加 WITH ROLLUP 可以在分组统计的基础上，额外生成上一层级的汇总行及全局总计行：

sql

SELECT department_id, job_title, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id, job_title WITH ROLLUP;

~~使用规范~~：

ONLY_FULL_GROUP_BY 模式：MySQL 5.7 及以上版本默认开启该 SQL 模式。SELECT 列表中出现的非聚合列，必须全部包含在 GROUP BY 字段列表中，否则会触发语法报错。
NULL 值归类：如果用于分组的字段包含 NULL 值，所有为 NULL 的记录会被归并为一个独立的分组进行聚合。
HAVING 与 WHERE 的分工：
- WHERE 在分组前执行，过滤基表原始行，不能包含聚合函数。
- HAVING 在分组后执行，过滤聚合汇总后的虚拟结果，支持聚合函数。
性能优化：尽量为 GROUP BY 涉及的字段建立联合索引，避免触发 Using temporary（使用临时表）和 Using filesort（文件排序）。

ORDER BY

ORDER BY 子句用于对检索出的数据结果集按照指定的字段或表达式进行升序（ASC）或降序（DESC）排序。

~~基本语法~~：

ORDER BY 位于 WHERE 或 HAVING 之后，LIMIT 之前：

sql

SELECT 字段列表
FROM 表名
[WHERE 条件表达式]
[GROUP BY 分组字段 [HAVING 分组条件]]
ORDER BY 排序字段1 [ASC | DESC], 排序字段2 [ASC | DESC] ...
[LIMIT 起始索引, 记录数];

ASC（Ascending）：升序排序（默认模式，可省略）。
DESC（Descending）：降序排序。

~~执行机制~~：

在 SQL 的逻辑执行生命周期中，ORDER BY 处于靠后的阶段：

获取数据集：经由 FROM、WHERE、GROUP BY、HAVING 过滤与聚合，并由 SELECT 提取投影字段及生成列别名。
选择排序模式：
- 索引排序（Using index）：如果排序字段命中了有序索引，存储引擎直接按索引顺序读取数据，无需额外计算。
- 文件排序（Using filesort）：未命中索引时，MySQL 会在内存的 sort_buffer 中进行排序；若数据量超出缓存大小，则借助磁盘临时文件采用归并排序。
确定排序算法：
- 全字段排序：将需要查询的所有列放入内存缓冲区排序，排序后直接输出结果，无需回表。
- Rowid 排序：单行数据过长时，仅将排序键和主键 ID 放入缓冲区排序，排好序后再通过主键 ID 回表获取完整字段。
传递截取：排序完成后，将有序结果集移交给 LIMIT 进行分页截取。

~~常见用法~~：

1. 单字段与多字段排序

多字段排序时，系统会优先按第一个字段排序；只有当第一字段值相同时，才会按第二个字段排序。

sql

-- 优先按部门升序，部门相同时按入职日期倒序
SELECT id, name, department_id, hire_date
FROM employees
ORDER BY department_id ASC, hire_date DESC;

2. 别名与表达式排序

由于 ORDER BY 在 SELECT 之后执行，可以直接使用 SELECT 中定义的别名或数学表达式：

sql

-- 按计算后的年总收入降序排列
SELECT name, salary, (salary * 12 + bonus) AS annual_income
FROM employees
ORDER BY annual_income DESC;

3. 自定义规则排序

借助 FIELD() 函数或 CASE WHEN 表达式，可以打破常规字母或数字顺序，指定业务权重规则：

sql

-- 按照状态优先级（PROCESSING -> PENDING -> COMPLETED）自定义排序
SELECT order_id, order_status
FROM orders
ORDER BY FIELD(order_status, 'PROCESSING', 'PENDING', 'COMPLETED');

~~注意事项~~：

NULL 值的排序规则：MySQL 将 NULL 视为最小值。在升序（ASC）时 NULL 排在最前，降序（DESC）时 NULL 排在最后。如需手动控制，可借助 ISNULL(col) 或 COALESCE 调整。
深分页排序性能（ORDER BY + LIMIT）：在无唯一键约束或大偏移量（如 LIMIT 1000000, 10）场景下，全表文件排序会导致严重的性能瓶颈，建议结合主键覆盖索引或子查询延迟关联优化。
联合索引的最左前缀原则：若需利用索引消除文件排序（避免 Using filesort），ORDER BY 字段的顺序、升降序规则必须与联合索引严格保持一致。

LIMIT

LIMIT 子句是 MySQL 中用于限制查询结果返回行数的专用子句，常用于实现客户端分页展示与截取头部（Top-N）数据。

~~基本语法~~：

LIMIT 始终位于整个 DQL 语句的最末尾：

sql

SELECT 字段列表
FROM 表名
[WHERE 条件列表]
[GROUP BY 分组字段 [HAVING 过滤条件]]
[ORDER BY 排序字段 [ASC | DESC]]
LIMIT [起始索引,] 查询记录数;

两种标准书写形式：

单参数语法：LIMIT count，表示从第 0 条开始截取前 count 条记录。
双参数语法：LIMIT offset, count 或 LIMIT count OFFSET offset，表示跳过前 offset 条记录，连续获取 count 条。

分页参数的数学映射关系：

业务参数	数据库计算公式	示例（每页 10 条）	对应 SQL
第 1 页	`offset = 0, count = 10`	提取 1 至 10 条	`LIMIT 0, 10`（或 `LIMIT 10`）
第 2 页	`offset = (2 - 1) * 10 = 10`	提取 11 至 20 条	`LIMIT 10, 10`
第 $n$ 页	`offset = (n - 1) * pageSize`	提取对应区间记录	`LIMIT (n - 1) * pageSize, pageSize`

~~执行机制~~：

LIMIT 是 SQL 逻辑执行生命周期中的最后一步：

接收有序结果集：接收由 ORDER BY 排序或 SELECT 投影生成的中间虚拟表。
游标顺序移动：存储引擎或执行器顺序读取数据行，逐行跳过前 offset 条记录（被跳过的行在底层依然会被读取）。
抓取有效行：读取紧随其后的 count 条有效记录，装载至客户端返回缓冲区。
提前终止（Early Exit）：一旦读取并填充满指定的 count 条记录，执行器立即中断后续数据的扫描与处理，直接将结果集返回。

~~常见用法~~：

1. 头部数据截取

结合 ORDER BY 快速获取排名前几位的极值数据：

sql

-- 查询薪资最高的前 3 名员工
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

2. 数据分页查询

在 Web 开发中实现按页拉取数据：

sql

-- 查询第 3 页数据，每页展示 20 条
SELECT id, title, create_time
FROM articles
ORDER BY id DESC
LIMIT 40, 20;

3. 快速探测

用于判断某类数据是否存在，避免全表扫描：

sql

-- 只要找到 1 条符合条件的记录即停止检索
SELECT id
FROM users
WHERE email = 'user@example.com'
LIMIT 1;

~~性能优化~~：

1. 深分页性能瓶颈

当偏移量巨大时（如 LIMIT 1000000, 10），MySQL 底层必须扫描并处理 1,000,010 行记录，随后丢弃前 1,000,000 行。若涉及回表操作，将导致极高的磁盘 I/O 开销。

2. 子查询延迟关联

利用主键覆盖索引快速定位 ID 范围，再通过主表连接获取完整字段：

sql

-- 优化前：产生大量回表
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

-- 优化后：仅在覆盖索引上扫描偏移量，最后回表 10 次
SELECT o.*
FROM orders o
JOIN (
  SELECT id FROM orders ORDER BY id LIMIT 1000000, 10
) AS tmp ON o.id = tmp.id;

3. 游标记录寻址

在连续翻页场景下，记录上一页最后一条数据的主键 ID，将分页转化为范围查询：

sql

-- 直接基于索引范围检索，彻底消除 OFFSET 扫描成本
SELECT *
FROM orders
WHERE id > 1000000
ORDER BY id ASC
LIMIT 10;

4. 排序稳定性保证

若 ORDER BY 的排序字段值存在重复（非唯一键），MySQL 优化器可能因执行路径差异导致不同页出现重复或遗漏记录。必须在排序条件后追加主键等唯一字段（如 ORDER BY create_time DESC, id DESC）。

多表联查

多表联查（Multi-Table Join）用于通过表与表之间的逻辑关联关系（外键或公共字段），从多个数据表中同时检索并整合数据。

连接本质

多表联查的底层数学基础是笛卡尔积（Cartesian Product）。当两张表进行关联且未指定连接条件时，结果集行数为两表行数的乘积（ $M \times N$ ）。

多表联查的核心逻辑是：通过 ON 条件在笛卡尔积生成的虚拟组合中，筛选出满足关联约束的有效数据行。

条件过滤

多表联查中，ON 子句与 WHERE 子句具有不同的执行阶段与过滤语义：

维度	ON 子句	WHERE 子句
执行时机	连接生成临时中间表时执行	中间表生成后执行
过滤作用	决定关联行是否匹配	对连接后的整个结果集进行行过滤
外连接特性	条件不满足时，主表依然保留行并补 `NULL`	条件不满足时，整行数据被直接丢弃

在 LEFT JOIN 中，若将右表的非空过滤条件写在 WHERE 中（例如 WHERE d.department_name = '研发部'），会导致左连接退化为内连接，丢失左表中未匹配的记录。

底层算法

MySQL 优化器在执行多表连接时，底层主要依赖以下连接算法：

索引嵌套循环（Index Nested-Loop Join, INLJ）：
- 驱动表遍历每行数据，被驱动表直接通过索引查找匹配行。
- 效率最高，时间复杂度接近 $O(N \log M)$ 。
块嵌套循环（Block Nested-Loop Join, BNL）：
- 在被驱动表无索引时，将驱动表数据分批加载到 join_buffer 内存块中，扫描被驱动表逐行对比。
- 减少被驱动表的全表扫描次数，但计算量依然较大。
哈希连接（Hash Join）：
- MySQL 8.0.18+ 引入，全面替代 BNL 算法。
- 在内存中将小表构建为哈希表，再扫描大表进行哈希匹配，大幅降低无索引连接的 CPU 开销。

优化准则

小表驱动大表：使用数据量小或经过 WHERE 过滤后结果集小的表作为驱动表，减少外层循环次数。
为关联字段建立索引：被驱动表的关联键（ON 涉及字段）必须建立高效索引，确保走 INLJ 算法。
保证关联字段类型严格一致：连接字段若存在类型不一致（如 VARCHAR 与 INT，或字符集/排序规则不同），会触发隐式类型转换导致索引失效。
严格控制 JOIN 表数量：联表数量尽量控制在 3 张以内。过多联表会导致优化器评估执行路径的计算复杂度呈指数级上升。

内连接 INNER JOIN

内连接（INNER JOIN）是多表联查中最基础、最常用的连接方式。它基于指定的关联条件对多张表进行匹配，仅返回两张表中完全匹配条件的交集记录。任何在另一张表中未找到匹配项的数据行都会被直接过滤丢弃。

~~语法形式~~：

内连接支持显式与隐式两种书写方式，在实际开发中推荐使用语义更清晰的显式语法。

连接形式	语法结构	特点说明
显式内连接（推荐）	`SELECT 字段 FROM 表A [INNER] JOIN 表B ON 关联条件;`	`INNER` 关键字可省略；连接条件与业务过滤条件隔离
隐式内连接	`SELECT 字段 FROM 表A, 表B WHERE 关联条件;`	多表用逗号分隔，条件写在 `WHERE` 中；易遗漏条件导致笛卡尔积

~~执行流程~~：

在 MySQL 逻辑执行层面，内连接的处理过程如下：

确定驱动表与被驱动表：优化器根据表的数据量及索引情况，自动选择成本较低的表作为驱动表。
构建笛卡尔积组合：遍历驱动表中的记录，并与被驱动表的记录逐一进行配对组合。
计算 ON 连接条件：对每一组配对记录计算 ON 条件表达式，仅保留计算结果为 TRUE 的数据行。
执行行级与投影过滤：对匹配成功的中间结果集执行 WHERE 条件筛选，并根据 SELECT 提取指定列输出。

~~场景示例~~：

以“员工表（employees）”与“部门表（departments）”关联为例，查询已分配部门的员工信息：

sql

SELECT
  e.id AS emp_id,
  e.name AS emp_name,
  d.department_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
WHERE e.status = 'ACTIVE';

若某员工 dept_id 为 NULL 或对应的部门不存在，该员工记录不会出现在结果集中。
若某部门下没有任何员工，该部门记录同样不会出现在结果集中。

~~优化建议~~：

驱动表选择：MySQL 优化器通常会自动选择“小表驱动大表”（即经过 WHERE 过滤后结果集较小的表作为驱动表），在复杂查询中可通过 STRAIGHT_JOIN 强制指定驱动顺序。
关联字段建立索引：确保被驱动表的关联键（如外键 d.id）建立了高效索引，以触发索引嵌套循环连接（Index Nested-Loop Join），避免全表扫描。
字段类型严格一致：连接字段的类型、长度及字符集（如 utf8mb4）必须完全一致，防止发生隐式类型转换导致索引失效。

左外连接 LEFT JOIN

~~左外连接（LEFT [OUTER] JOIN）~~ 是以左表为主表（完全显示） 的多表联查方式。无论右表中是否存在匹配记录，左表的所有行都会完整保留；若右表中未找到符合连接条件的记录，则右表字段在结果集中全部填充为 NULL。

~~基本语法~~：

OUTER 关键字通常可省略，语法结构如下：

sql

SELECT 字段列表
  FROM 左表 [AS 别名]
  LEFT [OUTER] JOIN 右表 [AS 别名] ON 关联条件
  [WHERE 过滤条件];

~~执行流程~~：

在 MySQL 逻辑处理中，左外连接的执行过程如下：

确立驱动关系：将 FROM 后的左表确定为主表（驱动表），LEFT JOIN 后的右表确定为被驱动表。
逐行关联匹配：遍历左表的每一条数据，依据 ON 条件与右表记录进行比对组合。
空值补齐（Null-Padding）：若左表某行在右表中未找到任何匹配记录，系统保留该左表行，并将对应的右表所有字段填充为 NULL 拼入中间表。
执行后续过滤：对生成的连接结果集执行 WHERE 条件过滤，最后执行 SELECT 投影输出。

~~应用场景~~：

1. 主从数据全量呈现

用于查询基础主体的全量信息，同时附加关联属性。

sql

-- 查询所有员工信息及其所在部门（未分配部门的员工也会正常列出）
SELECT e.id, e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

2. 差集排他查询

结合 WHERE 右表.主键 IS NULL，可以快速筛选出“只存在于左表但不存在于右表”的数据（Anti-Join）。

sql

-- 查询从未下过订单的用户列表
SELECT u.id, u.username
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

~~核心陷阱~~：

WHERE 条件导致连接退化：若在 WHERE 子句中添加了针对右表字段的常规条件（如 WHERE d.status = 1），由于 NULL 值无法通过条件判定，未匹配的左表数据会被直接丢弃，导致左连接隐式退化为内连接（INNER JOIN）。右表的过滤条件应优先写在 ON 子句中。
数据一对多膨胀：若左表与右表存在 1:N 的关联关系，连接后左表原本的单行记录会被复制展开为多行，需警惕后续聚合统计（如 SUM）时的重复计算。
驱动表优化限制：与内连接（优化器可自由调换驱动顺序）不同，左连接在绝大多数场景下强制以左表作为驱动表，因此必须确保右表的关联字段建立了高效索引。

右外连接 RIGHT JOIN

~~右外连接（RIGHT [OUTER] JOIN）~~ 是以右表为主表的多表联查方式。它会完整保留右表中的全部记录；当左表中不存在满足连接条件的匹配记录时，结果集中左表的对应字段将自动填充为 NULL。

~~基本语法~~：

OUTER 关键字为可选语法，标准结构如下：

sql

SELECT 字段列表
FROM 左表 [AS 别名]
RIGHT [OUTER] JOIN 右表 [AS 别名] ON 关联条件
[WHERE 过滤条件];

~~执行机制~~：

右外连接在 MySQL 逻辑处理中的执行流程如下：

确立基准主表：将 RIGHT JOIN 后的右表确立为主表（驱动表），FROM 后的左表作为从表（被驱动表）。
逐行关联比对：遍历右表的每一条记录，依据 ON 条件在左表中寻找匹配的数据行。
空值补齐（Null-Padding）：
- 匹配成功：将左右两表的匹配字段拼接并保留。
- 匹配失败：完整保留该右表记录，左表的所有字段统一填充为 NULL 进行拼接。
后续过滤与投影：对合并后的中间结果集执行 WHERE 行级条件过滤，最后由 SELECT 提取指定字段输出。

~~典型示例~~：

1. 全量维度查询：

以“员工表（employees）”与“部门表（departments）”关联为例，查询所有部门及部门下属员工：

sql

-- departments 为右表（主表），即使某部门下暂无员工，该部门也会被列出
SELECT
  d.id AS dept_id,
  d.department_name,
  e.name AS employee_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

2. 差集排他过滤：

结合 WHERE 左表.主键 IS NULL，可以快速筛选出“存在于右表但未关联左表”的数据（Anti-Join）：

sql

-- 查询没有任何员工的空置部门
SELECT d.id, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id
WHERE e.id IS NULL;

~~工程转换~~：

在实际项目开发与数据库开发规范中，**通常推荐统一使用 LEFT JOIN 代替 RIGHT JOIN**。

阅读认知顺畅：代码阅读习惯是从左向右、从上到下。使用 LEFT JOIN 时位于 FROM 后的首表即为主表，语义更加直观。

等价转换规则：任何右外连接均可通过调换表的位置转换为左外连接，两者在优化器解析后的执行效率完全一致：

sql

-- 右连接写法
SELECT * FROM table_a a RIGHT JOIN table_b b ON a.id = b.a_id;

-- 等价的左连接写法（推荐）
SELECT * FROM table_b b LEFT JOIN table_a a ON b.a_id = a.id;

~~核心注意~~：

WHERE 条件导致连接退化：若在 WHERE 子句中直接对左表字段施加过滤条件（如 WHERE e.status = 'ACTIVE'），由于未匹配行的左表字段均为 NULL，无法通过常规条件判断，会导致未匹配的右表数据被直接丢弃，使得右连接隐式退化为内连接（INNER JOIN）。
索引依赖方向：在右连接中，右表为主表进行全量扫描或范围扫描，被驱动的左表关联字段必须建立索引，以确保触发索引嵌套循环连接（Index Nested-Loop Join），降低 I/O 消耗。

全外连接 FULL JOIN

全外连接（FULL [OUTER] JOIN）是多表联查中用于获取两表完全并集的连接方式。无论数据在左表还是右表，只要存在记录都会被完整保留；若某一行在另一张表中无匹配记录，对应的未匹配字段全部填充为 NULL。

~~核心特性~~：

完整并集保留：包含左表独有数据、右表独有数据以及两表共同匹配的交集数据。
双向空值填充：左表未匹配记录的右侧字段补 NULL，右表未匹配记录的左侧字段补 NULL。
MySQL 语法支持：MySQL 原生不支持 FULL JOIN 或 FULL OUTER JOIN 关键字，在实际应用中需要通过 LEFT JOIN、RIGHT JOIN 配合 UNION 算子模拟实现。

~~实现语法~~：

1. 基础模拟写法：

通过 UNION 自动合并左外连接与右外连接的结果集，并去除重复的交集记录：

sql

SELECT e.name AS emp_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id

UNION

SELECT e.name AS emp_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

2. 高性能模拟写法：

利用 UNION ALL 结合差集过滤，避免全量哈希或排序去重的性能开销（推荐）：

sql

-- 1. 获取全部左表数据 + 匹配的右表数据
SELECT e.name AS emp_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id

UNION ALL

-- 2. 仅追加右表独有数据（排除交集）
SELECT e.name AS emp_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id
WHERE e.id IS NULL;

~~执行机制~~：

以“高性能模拟写法”为例，MySQL 逻辑执行全外连接的步骤如下：

执行左外连接：遍历左表并匹配右表，生成包含“交集”与“左表独有”的数据集。
提取右表独有差集：执行右外连接，并通过 WHERE 左表.主键 IS NULL 仅提取右表未匹配的孤立行。
流式集合拼接：执行 UNION ALL 算子，将两个互斥的结果集直接拼接装入输出缓冲区，无需构建去重临时表。
输出最终结果集：将合并后的完整投影数据返回给客户端。

~~性能优化~~：

优先采用 UNION ALL：标准的 UNION 会隐式创建内存/磁盘临时表对全量数据执行去重排序，数据量大时 I/O 成本高昂；采用“左连接 + 右连接排他”配合 UNION ALL 可彻底消除去重开销。
双向关联索引：由于左连接与右连接交替以两表作为驱动表，两表的关联键（ON 字段）均需建立高效索引。
避免在 WHERE 中写常规非空条件：针对两表的常规过滤条件若写在全局 WHERE 中，会因为空值无法通过校验导致全外连接退化为内连接。

自连接 SELF JOIN

自连接（SELF JOIN）是指同一张数据表与其自身进行关联查询的操作。在逻辑上，MySQL 会通过为同一张表赋予不同的别名，将其虚拟为两张独立的表来进行关联匹配与计算。

~~基本语法~~：

进行自连接时，必须为表显式指定不同的别名，否则会导致字段名冲突：

sql

-- 自连接的内连接写法
SELECT a.字段1, b.字段2
FROM 表名 a
JOIN 表名 b ON a.关联键 = b.关联键
[WHERE 过滤条件];

-- 自连接的左外连接写法（保留无匹配记录的根节点）
SELECT a.字段1, b.字段2
FROM 表名 a
LEFT JOIN 表名 b ON a.父级键 = b.主键;

~~执行机制~~：

MySQL 在底层处理自连接的执行流程如下：

虚拟视图构建：优化器根据表别名在内存中实例化两个逻辑表引用（如表 A 与表 B）。
生成笛卡尔积：将表 A 的每一行与表 B 的所有行进行笛卡尔积组合。
ON 条件筛选：依据连接条件（通常是一列等于同一表的另一列，如 a.parent_id = b.id）过滤出有效关联行。
行级过滤与投影：执行 WHERE 子句中额外的行级约束，最终由 SELECT 提取指定别名表中的列输出。

~~经典场景~~：

1. 组织架构与上下级查询：

常用于存储在单表中的树形结构（如员工与直属领导）：

sql

-- 查询员工姓名及其直接领导姓名（最高领导无上级，采用 LEFT JOIN 保留）
SELECT
  emp.id AS emp_id,
  emp.name AS emp_name,
  mgr.name AS manager_name
FROM employees emp
LEFT JOIN employees mgr ON emp.manager_id = mgr.id;

2. 同表记录横向对比：

用于筛选同一张表中满足特定相对关系的记录（如查询薪资高于同部门其他人的员工，或查找同一商品不同批次）：

sql

-- 查询同一部门中入职时间比当前员工更早的同事列表
SELECT
  e1.name AS current_employee,
  e2.name AS senior_colleague,
  e1.department_id
FROM employees e1
JOIN employees e2 ON e1.department_id = e2.department_id
        AND e1.hire_date > e2.hire_date;

3. 节点层级关系展开：

用于获取层级分类（如商品分类表中的一级分类与二级分类）：

sql

-- 查询二级分类及其所属的一级分类名称
SELECT
  parent.category_name AS level1_name,
  child.category_name AS level2_name
FROM categories child
JOIN categories parent ON child.parent_id = parent.id;

~~注意事项~~：

表别名强制性：自连接中涉及相同表名，所有字段引用必须带有表别名前缀（如 a.col），否则会抛出 Column '...' in field list is ambiguous 报错。
连接类型选择：
若使用 INNER JOIN，树形结构的根节点（如顶级领导、根分类 parent_id IS NULL）会被直接丢弃。
若需保留完整层级树，必须使用 LEFT JOIN。
深层递归性能瓶颈：自连接仅适用于固定层级（如 2 到 3 层）的关系查询。若层级深度不确定或较深，多次自连接会导致性能急剧下降，在 MySQL 8.0+ 中建议改用 递归 CTE（WITH RECURSIVE） 实现。
双向索引依赖：自连接的关联字段（如主键 id 和外键 parent_id）两端均需建立高效索引，以确保走索引嵌套循环算法，避免全表扫描。

交叉连接 CROSS JOIN

交叉连接（CROSS JOIN）是多表联查中的笛卡尔积（Cartesian Product）运算。在不指定任何关联条件时，它将左表的每一行与右表的每一行进行全排列组合。若左表有 $M$ 行、右表有 $N$ 行，交叉连接的结果集总行数即为 $M \times N$ 。

~~语法形式~~：

在 MySQL 中，CROSS JOIN 支持显式与隐式两种书写方式：

语法形式	SQL 示例	说明
显式交叉连接	`SELECT * FROM table_a CROSS JOIN table_b;`	语义明确，明确表示主动生成笛卡尔积
隐式交叉连接	`SELECT * FROM table_a, table_b;`	多表直接用逗号分隔且不带 `WHERE` 条件

在 MySQL 实现中，CROSS JOIN、INNER JOIN 和 JOIN 在语法层面是等价的：若不带 ON 条件，三者均表现为交叉连接；若添加了 ON 条件，则均按内连接逻辑执行。

~~执行机制~~：

MySQL 执行无条件交叉连接时的底层处理步骤如下：

确定驱动顺序：优化器通常选取数据量较小或行占用空间较小的表作为驱动表。
嵌套循环组合：依次读取驱动表的每一行记录，针对该行全量扫描被驱动表的所有记录。
数据行拼接：将驱动表当前行与被驱动表的每一行字段组合拼接为新的数据行。
流式输出：将拼接后的组合行直接装入结果集缓冲区并交付给上层投影输出，直至两表全部遍历完成。

~~典型场景~~：

1. 笛卡尔积矩阵生成：

常用于电商商品 SKU 生成等需要全量属性排列组合的业务：

sql

-- 生成所有颜色与尺码的组合矩阵
SELECT
  c.color_name,
  s.size_name
FROM colors c
CROSS JOIN sizes s;

2. 报表维度补全：

在按天/按月统计报表时，为了防止某些日期因无业务数据而断档，常将日历维度表与主体表进行交叉连接以补全空缺维度：

sql

-- 生成每个门店在当月所有日期的基准骨架
SELECT
  s.store_id,
  d.report_date
FROM stores s
CROSS JOIN calendar_dates d
WHERE d.report_date BETWEEN '2026-08-01' AND '2026-08-31';

3. 批量数据构造：

在开发测试环境中，利用交叉连接对现有数据进行翻倍膨胀，快速生成百万级测试数据：

sql

-- 将原表数据自乘膨胀生成大批量测试记录
INSERT INTO test_benchmark (val)
SELECT t1.val
FROM test_data t1
CROSS JOIN test_data t2;

~~核心风险~~：

数据量几何级膨胀：两个包含 10,000 行的表进行交叉连接，会产生 1 亿行数据，极易耗尽服务器 CPU、内存及网络带宽。
开发误操作风险：在编写多表联查时，若疏漏了 ON 连接条件或 WHERE 关联子句，查询会隐式退化为笛卡尔积，引发线上慢查询甚至数据库雪崩。
优化器局限性：无条件的交叉连接无法借助索引进行范围缩减，必须执行全表扫描。

子查询

子查询（Subquery）指嵌套在其他 SQL 查询语句内部的 SELECT 语句，也称为嵌套查询（Nested Query）。外部查询称为外层查询或父查询，内部嵌套的查询称为内层查询或子查询。

出现位置

子查询在 DQL 语句中可放置在不同子句中，各自承担不同的功能：

SELECT 后面：只能是标量子查询，作为计算列投影输出。
FROM 后面：作为派生表使用，必须赋予别名。
WHERE / HAVING 后面：作为条件过滤依据（标量、列或行子查询）。

执行流程

在 MySQL 查询执行器层面，不同类型子查询的处理步骤如下：

不相关子查询执行：
- 优先执行内层查询，生成单值、列表或物化临时表。
- 外层查询将物化结果作为静态条件进行过滤或关联。
相关子查询执行：
- 外层查询读取一行候选记录。
- 将候选记录的相关字段传入内层子查询中执行求值。
- 根据内层返回的布尔值或计算结果决定是否保留外层候选行。
- 重复上述步骤直到外层数据遍历完毕。

常见陷阱与优化

NOT IN 与 NULL 陷阱：若 NOT IN 子查询的结果集中包含哪怕一个 NULL 值，整个 NOT IN 条件的计算结果将始终为 UNKNOWN（即返回空集）。应改用 NOT EXISTS 或在子查询中显式添加 WHERE col IS NOT NULL。
子查询改写为 JOIN：对于大数据量场景，WHERE col IN (SELECT ...) 可能无法被有效优化，改写为 INNER JOIN 或 LEFT JOIN ... IS NULL 可以更好地利用被驱动表索引。
派生表性能消耗：放在 FROM 后的表子查询在 MySQL 8.0 之前会强制物化为临时表；MySQL 8.0 引入了派生表合并（Derived Table Merge）优化，但在包含聚合、DISTINCT 或 LIMIT 时仍会物化，需注意临时表内存上限（tmp_table_size）。

结果分类

标量子查询

标量子查询（Scalar Subquery）是指执行结果为单一数值（一行一列，即 $1 \times 1$ ）的子查询。由于其返回的是标量数据，在语法上可以像常规常量、变量或单一字段表达式一样，直接嵌入到 SQL 语句中支持单值计算的任意位置。

~~出现位置~~：

标量子查询在 DQL 中常见的放置位置包括：

~~WHERE / HAVING 条件~~：

与单值比较运算符（=, !=, >, <, >=, <=）组合，作为动态过滤阈值：

sql

-- 查询薪资高于全公司平均薪资的员工
SELECT id, name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

~~SELECT 投影列表~~：

作为动态计算列输出。通常与外层表建立关联（相关子查询）：

sql

-- 查询员工信息及其实际所在部门的名称
SELECT
  e.id,
  e.name,
  (SELECT d.department_name FROM departments d WHERE d.id = e.dept_id) AS dept_name
FROM employees e;

~~ORDER BY 排序子句~~：

基于子查询计算出的单值权重对外部结果集进行排序：

sql

-- 按照每个部门的平均薪资由高到低对员工进行排序
SELECT name, dept_id, salary
FROM employees e
ORDER BY (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id) DESC;

~~执行流程~~：

在 MySQL 查询引擎中，标量子查询根据是否依赖外层字段，分为两种执行机制：

1. 不相关标量子查询

单次预计算：优化器在执行外层主查询前，优先执行内层子查询。
标量常量化：子查询计算出单一结果（如平均值 8500），并将其作为静态常量注入外层上下文。
主查询过滤：外层扫描数据行，直接与该常量进行比较过滤，无需重复执行内层查询。

2. 相关标量子查询

逐行迭代驱动：外层查询逐行扫描候选记录。
参数传递求值：将外层当前行的字段（如 e.dept_id）传入内层子查询中计算单值结果。
结果回填计算：将内层返回的标量值用于当前行的投影或条件判断。
循环执行：重复上述过程直至外层全部记录处理完毕。

~~核心陷阱~~：

多行返回错误（Error 1242）：标量子查询在语法上严格要求结果最多只能有 1 行 1 列。若运行期间内层查询匹配出 2 行及以上数据，MySQL 会直接抛出 Subquery returns more than 1 row 运行时异常。
空值处理（NULL）：若标量子查询未检索到任何符合条件的记录，其返回值自动置为 NULL。在 WHERE col = (SELECT ...) 中，当子查询为 NULL 时，比较判定结果为 UNKNOWN，会导致该行被过滤。
SELECT 投影性能损耗：在 SELECT 列表中使用相关标量子查询相当于执行了 $N$ 次循环查询（N+1 问题）。当主表数据量达到万级以上时，应将其改写为 LEFT JOIN 配合分组聚合以提升性能。

列子查询

列子查询（Column Subquery）是指子查询的执行结果为一列多行（即 $N \times 1$ ）的子查询。其本质是一个一维数据集合，通常置于 WHERE 或 HAVING 子句中，与集合操作符或量词配合进行多值比较。

~~常用操作符~~：

列子查询不能直接使用常规的单值比较运算符（如 =、>），必须配合专门的集合操作符：

操作符	语法组合	匹配逻辑与等价含义
`IN`	`列 IN (列子查询)`	只要等于子查询列表中的任意一个值，即判定为 `TRUE`
`NOT IN`	`列 NOT IN (列子查询)`	不等于子查询列表中的所有值，才判定为 `TRUE`
`ANY` / `SOME`	`列 > ANY (列子查询)`	大于子查询返回列表中的最小值（满足任意一个即可）
`ANY` / `SOME`	`列 < ANY (列子查询)`	小于子查询返回列表中的最大值
`ALL`	`列 > ALL (列子查询)`	大于子查询返回列表中的最大值（必须满足全部）
`ALL`	`列 < ALL (列子查询)`	小于子查询返回列表中的最小值

~~语法与示例~~：

~~IN / NOT IN 集合匹配~~：

用于筛选存在或不存在于特定关联集合中的数据：

sql

-- 查询“研发部”和“市场部”所有员工的姓名与薪资
SELECT name, salary
FROM employees
WHERE dept_id IN (
  SELECT id FROM departments WHERE department_name IN ('研发部', '市场部')
);

~~ANY / SOME 相对比较~~：

用于满足集合中任一条件的区间匹配：

sql

-- 查询薪资比“研发部任意一名员工”薪资更高的其他部门员工
-- 逻辑等价于：salary > MIN(研发部薪资)
SELECT name, salary, dept_id
FROM employees
WHERE salary > ANY (
  SELECT salary FROM employees WHERE dept_id = 101
) AND dept_id != 101;

~~ALL 全量比较~~：

用于满足集合中所有条件的极值匹配：

sql

-- 查询薪资比“研发部所有员工”薪资都要高的其他部门员工
-- 逻辑等价于：salary > MAX(研发部薪资)
SELECT name, salary, dept_id
FROM employees
WHERE salary > ALL (
  SELECT salary FROM employees WHERE dept_id = 101
) AND dept_id != 101;

~~执行流程~~：

在 MySQL 查询执行器层面，列子查询的逻辑处理步骤如下：

子查询求值与物化：优先计算内层查询生成单列数据集；若命中优化规则，系统会在内存中构建物化临时表（Materialization）并建立临时哈希索引。
半连接重写转换：对于 IN 子查询，MySQL 优化器通常会将其重写为 Semi-Join（半连接） 架构，以便利用连接算法进行快速查找。
逐行扫描匹配：外层查询遍历数据行，提取待比较字段值在物化索引或子查询结果中进行哈希查找或量词判定。
过滤与投影输出：剔除判定为 FALSE 或 UNKNOWN 的行，将满足条件的外层记录移交给 SELECT 输出。

~~核心陷阱与优化~~：

NOT IN 与 NULL 空值陷阱：若列子查询的结果集中包含至少一个 NULL 值，NOT IN 的布尔判定结果会退化为 UNKNOWN，导致整个外层查询直接返回空结果集。防范方案是必须在子查询中添加 WHERE 列 IS NOT NULL，或改用 NOT EXISTS。

改写为 INNER JOIN 提升性能：当子查询返回数据量较大时，IN 可能引发多次扫描。改写为 INNER JOIN 或 EXISTS 能够更好地利用被驱动表上的现有索引：

sql

-- 改写前（IN 列子查询）
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status = 1);

-- 改写后（JOIN 连接）
SELECT o.*
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 1;

行子查询

行子查询（Row Subquery）是指执行结果为一行多列（即 $1 \times N$ ）的子查询。其返回的数据本质上是一个多维元组（Tuple），通常位于 WHERE 或 HAVING 子句中，用于同时对多个字段进行联合比对与条件过滤。

~~基本语法~~：

行子查询使用括号将多个字段构建为元组形式，与子查询返回的一行多列结果进行匹配：

sql

SELECT 字段列表
FROM 表名
WHERE (字段1, 字段2, ...) 比较运算符 (
  SELECT 字段1, 字段2, ...
  FROM 表名
  WHERE 单行过滤条件
);

常用比较运算符包括：=, != / <>, IN, NOT IN。

~~执行机制~~：

在 MySQL 查询引擎中，行子查询的处理流程如下：

子查询求值：优先执行内层查询，获取包含多个字段的单行记录（元组值）。
元组结构封装：外层查询遍历数据表，将当前行的目标比较字段按相同顺序封装为元组。
多维属性比对：对两个元组中的对应位置字段逐一进行比较计算。
行级过滤判定：
- 所有对应字段的比较结果均满足运算符逻辑时，判定为 TRUE 并保留该行。
- 任一字段不匹配时，判定为 FALSE 或 UNKNOWN 并丢弃该行。

~~典型场景~~：

1. 多字段精确对齐：

用于查询与某个基准对象在多个维度上完全相同的记录：

sql

-- 查询与“张三”处于相同部门且职位相同的其他员工
SELECT name, dept_id, job_title, salary
FROM employees
WHERE (dept_id, job_title) = (
  SELECT dept_id, job_title
  FROM employees
  WHERE name = '张三'
) AND name != '张三';

2. 极值条件组合检索：

用于提取同时匹配多个聚合指标的单行记录：

sql

-- 查询入职时间最早且薪资最高的员工信息
SELECT id, name, hire_date, salary
FROM employees
WHERE (hire_date, salary) = (
  SELECT MIN(hire_date), MAX(salary)
  FROM employees
);

~~注意事项~~：

多行返回异常（Error 1242）：当使用 = 或 != 运算符时，子查询必须确保最多仅返回 1 行数据。若内层查询返回了 2 行及以上数据，系统会直接报错 Subquery returns more than 1 row。若预期结果为多行，需将 = 替换为 IN。
字段顺序与数量严格对齐：外层括号中的字段数量、数据类型及排列顺序，必须与内层子查询 SELECT 列表中的字段严格保持一致。
NULL 值的二义性：若外层字段或子查询结果中包含 NULL，元组比较规则会导致判定结果变为 UNKNOWN，无法被常规运算符匹配命中。
联合索引优化：为行子查询涉及的多个字段建立联合索引（Composite Index），可使外层比对直接在索引树上完成定位，避免回表与全表扫描。

表子查询

表子查询（Table Subquery）是指执行结果为多行多列（即 $M \times N$ ）的子查询。由于其返回的是一个完整的二维数据集，在逻辑上等价于一张实体表或视图，最常放置在 FROM 或 JOIN 子句中作为派生表（Derived Table）参与外层的关联与运算。

~~基本语法~~：

表子查询放置在 FROM 或 JOIN 之后，必须显式指定表别名：

sql

SELECT 外部字段列表
FROM (
  SELECT 内部字段列表
  FROM 源数据表
  WHERE 内部过滤条件
) AS 派生表别名
[JOIN 其他表 ON 关联条件]
[WHERE 外部过滤条件];

~~执行机制~~：

MySQL 查询引擎处理表子查询的流程如下：

子查询求值与物化：优先计算括号内的子查询，在内存中构建临时结果集（物化为临时表）。
派生表合并优化（Derived Merge）：在 MySQL 5.7 及以上版本中，若子查询不包含 GROUP BY、DISTINCT、LIMIT 或聚合函数，优化器会自动将子查询展开合并到外层查询中，消除临时表创建开销。
外层关联与计算：将生成的派生表作为数据源，与外层其他数据表执行 JOIN 连接、WHERE 过滤或二次聚合。
投影输出：根据外层 SELECT 提取指定列并返回给客户端。

~~典型场景~~：

1. 分组极值与原表回连

用于解决“取各分组内最大/最小记录完整明细”的经典业务问题：

sql

-- 查询各部门薪资最高的员工姓名及详细薪资
SELECT e.name, e.dept_id, e.salary
FROM employees e
JOIN (
  SELECT dept_id, MAX(salary) AS max_salary
  FROM employees
  GROUP BY dept_id
) AS dept_max
  ON e.dept_id = dept_max.dept_id
 AND e.salary = dept_max.max_salary;

2. 多阶段分步统计

用于需要对聚合统计指标进行二次汇总的分析场景：

sql

-- 统计各个职业的年收入总和与销售总额
SELECT
  sq.Occupation,
  SUM(sq.YearlyIncome) AS TotalIncome,
  SUM(sq.Sales) AS TotalSales
FROM (
  SELECT EmpID, FirstName, LastName, Occupation, YearlyIncome, Sales
  FROM EmployeeTb
  WHERE Sales > 500
) AS sq
GROUP BY sq.Occupation;

~~注意事项~~：

强制指定别名：在 FROM 子句中使用表子查询时，必须使用 AS 别名 为其命名，否则会触发错误 Every derived table must have its own alias。
临时表内存溢出：若派生表无法被优化器合并且数据量庞大，中间结果集超出 tmp_table_size 和 max_heap_table_size 上限时，会自动转换为磁盘临时表，导致显著的 I/O 延迟。
条件预先下推：尽量在内层子查询的 WHERE 中过滤数据，避免将全量原始数据加载到派生表中再进行外层过滤。
替代方案（CTE）：在 MySQL 8.0+ 中，复杂或多层嵌套的表子查询推荐使用 公用表表达式（CTE，WITH 语句） 编写，逻辑结构更清晰且支持同一结果集的多次复用。

复制方案	复制数据	复制索引	自增属性保留	约束保留	适用场景
`CREATE TABLE ... AS SELECT`	支持	仅复制列类型，丢失索引	丢失	仅保留非空/默认值	临时分析表、数据快照抽取
`CREATE TABLE ... LIKE`	不支持	完整保留全部索引	保留	保留全部约束（除外键）	快速构建相同结构的空表
`INSERT INTO ... SELECT`	支持	写入已有表的现有索引	沿用已有表规则	受已有表约束校验	历史数据归档、跨表批量迁移
`LIKE` + `INSERT ... SELECT`	支持	完整保留全部索引	保留	保留完整属性	数据库全量单表备份与克隆

属性类型	保留情况	详细说明
字段名称与数据类型	保留	自动继承结果集列名及推导的数据类型
表中数据行	保留	完整写入符合 `WHERE` 条件的记录行
默认值与 NOT NULL	部分保留	基础非空约束可继承，但表达式默认值可能丢失
主键（PRIMARY KEY）	丢失	新表不会设置主键，需手动补充
唯一索引 / 二级索引	丢失	所有索引定义均不会迁移到新表
自增属性（AUTO_INCREMENT）	丢失	自增列会退化为常规整数列
外键约束（FOREIGN KEY）	丢失	关联外键关系全部解除

若业务需要 100% 完整保留原表的主键、索引、自增属性以及全部数据，标准做法是采用两步分步执行：

sql

-- 第一步：基于 LIKE 复制完整结构与全部索引定义（无数据）
CREATE TABLE new_table LIKE old_table;

-- 第二步：使用 INSERT INTO ... SELECT 灌入全量数据
INSERT INTO new_table
SELECT * FROM old_table;

~~核心注意~~：

DDL 隐式提交：CTAS 属于 DDL 操作，无法在显式事务（BEGIN ... COMMIT）中通过 ROLLBACK 进行回滚。
锁与并发阻塞：在默认隔离级别下，CTAS 扫描源表时可能持有共享读锁（S-Lock），针对千万级大表执行时会长时间阻塞源表的并发写入。
计算列类型推导膨胀：若 SELECT 列表中包含函数运算或字符串拼接（如 CONCAT(col_a, col_b)），优化器可能会将字段类型推导为较大的 VARCHAR 或 TEXT，造成存储空间膨胀。

方案2：仅复制表结构

在 MySQL 中，仅复制表结构指在不导入任何数据记录的情况下，创建一张与源表结构相同或部分相同的空表。该操作常用于分表建表、临时中间表初始化以及测试基准环境搭建。

~~核心方案~~：

MySQL 中仅复制表结构主要有两种实现方式：

1. LIKE 语法：

使用 LIKE 关键字可以 1:1 完整复刻原表的物理表结构定义：

sql

CREATE TABLE new_table LIKE old_table;

完整继承：原表的主键、唯一索引、二级索引、自增属性（AUTO_INCREMENT）、字符集、字段默认值及注释均会被完整复制到新表。
数据状态：新表不包含任何原始数据行，且自增计数器会重置为初始状态。

2. CTAS 配合恒假条件：

利用 CREATE TABLE ... AS SELECT 配合恒假条件（如 WHERE 1 = 0），仅基于查询结果集的元数据生成空表：

sql

-- 复制全部字段结构（无索引）
CREATE TABLE new_table AS
SELECT * FROM old_table WHERE 1 = 0;

-- 抽取指定字段结构
CREATE TABLE partial_table AS
SELECT id, name, salary FROM old_table WHERE 1 = 0;

部分继承：仅继承查询列表中的字段名、数据类型和基础非空约束。
特性丢失：原表的主键、唯一索引、二级索引及 AUTO_INCREMENT 属性会全部丢失。

~~方案对比~~：

对比维度	`CREATE TABLE ... LIKE`	`CTAS + WHERE 1 = 0`
数据行	不复制（空表）	不复制（空表）
主键与索引	完整保留	全部丢失
自增属性（AUTO_INCREMENT）	保留（计数器重置）	丢失（退化为普通整型）
字段子集选择	不支持（强制全量列）	支持（SELECT 中自定义列）
存储引擎与字符集	严格继承原表	继承原表推导配置
适用场景	正式业务分表、完整结构克隆	临时报表、中间计算表抽取

~~执行流程~~：

以 CREATE TABLE ... LIKE 为例，MySQL 查询与元数据引擎的处理步骤如下：

读取源表元数据：从系统数据字典（Data Dictionary）中提取源表的完整结构定义。
构建物理结构：在新表命名空间下创建相同的字段、索引树定义与存储引擎参数。
分配存储空间：为新表分配物理表空间文件，并将自增计数器重置为默认起始值。
注册数据字典：将新表元数据持久化并隐式提交事务，完成空表创建。

~~注意事项~~：

外键约束不继承：无论使用 LIKE 还是 CTAS 语法，MySQL 均不会自动将原表的外键约束（FOREIGN KEY）复制到新表。
跨库结构复制：两种语法均支持跨库操作，格式为 CREATE TABLE target_db.new_table LIKE source_db.old_table;。
分区规则保留：使用 LIKE 复制分区表时，新表会自动继承原表的分区策略与配置定义。

方案3：复制数据到已有表

在 MySQL 中，向已有表复制数据通过 INSERT INTO ... SELECT 语句实现。它将查询检索出的数据集批量注入到已预先创建的目标表中，常用于历史数据归档、跨表汇总、ETL 数据转换等场景。

~~基本语法~~：

1. 全字段匹配插入：

当源表的查询列顺序、数量和类型与目标表完全一致时，可直接使用全列插入：

sql

INSERT INTO target_table
SELECT * FROM source_table
WHERE status = 'ARCHIVED';

2. 指定列映射插入：

当两表结构不完全一致，或仅需同步部分字段时，必须显式声明列映射关系（推荐写法）：

sql

INSERT INTO target_table (user_id, user_name, total_amount, create_time)
SELECT id, name, order_sum, NOW()
FROM source_table
WHERE is_active = 1;

~~冲突处理~~：

当源表数据与目标表的主键（PRIMARY KEY）或唯一索引（UNIQUE INDEX）发生冲突时，MySQL 提供两种常用的策略：

冲突策略	语法格式	行为说明
忽略冲突	`INSERT IGNORE INTO ... SELECT ...`	遇到主键/唯一键重复时直接跳过该行，不中断事务，继续写入后续数据
覆盖更新	`INSERT INTO ... SELECT ... ON DUPLICATE KEY UPDATE ...`	遇到主键/唯一键重复时，将冲突行按指定逻辑执行更新操作

sql

-- 遇到唯一键冲突时，累加金额并更新最后修改时间
INSERT INTO daily_stats (stat_date, category_id, sales_amount)
SELECT report_date, category_id, amount
FROM source_orders
ON DUPLICATE KEY UPDATE
  sales_amount = sales_amount + VALUES(sales_amount),
  updated_at = NOW();

~~执行流程~~：

MySQL 在底层执行 INSERT INTO ... SELECT 时的处理步骤如下：

解析与权限校验：校验用户对源表的 SELECT 权限以及目标表的 INSERT 权限，对齐两表字段映射元数据。
源表检索与加锁：
- 执行查询计划扫描源表，根据 WHERE 条件过滤记录。
- 在默认的 REPEATABLE READ 隔离级别下，InnoDB 存储引擎会对源表扫描涉及的行和间隙加共享读锁（S-Lock / Next-Key Lock）以保证一致性。
批量构建与约束校验：将读取到的行数据加载至写入缓冲区，对每行数据执行目标表的非空约束、外键约束及唯一键冲突校验。
目标表写入与索引维护：对目标表加排他写锁（X-Lock），将数据批量追加至聚簇索引树并同步更新二级索引。
事务提交与持久化：生成 Redo Log 与 Undo Log，写满或执行完毕后提交事务完成持久化。

~~性能优化~~：

大数据量分批写入：避免单次执行百万级数据插入（大事务会导致 Undo Log 膨胀、锁持有时间过长以及主从复制严重延迟）。建议基于源表主键进行切分：
sql
```
-- 推荐：按主键区间分批写入
INSERT INTO archive_orders (id, user_id, amount)
SELECT id, user_id, amount
FROM orders
WHERE id BETWEEN 1 AND 50000;
```
1
2
3
4
5
严格保证字段类型一致：源表 SELECT 出的列类型若与目标表列类型不匹配，会触发隐式类型转换，严重消耗 CPU 资源且可能导致精度丢失。
隔离级别与无锁优化：若业务允许，将事务隔离级别调整为 READ COMMITTED，降低源表的加锁粒度，防止长时间阻塞源表的并发写入。
临时禁用非唯一索引：在超大规模数据批量迁移前，可先在目标表删除或禁用二级索引，待全量写入完成后再重建索引，以降低实时维护 B+ 树的 I/O 成本。

方案4：完整高保真复制

完整高保真复制指对源表的结构定义（字段属性、主键、全部索引、自增属性、字符集、约束及注释）与全量数据进行 100% 完整复刻的方案，克服了常规 CREATE TABLE ... AS SELECT 语法丢失索引与物理特性的缺陷。

~~实现方案~~：

在实际开发中，完整高保真复制采用标准的两步分步法实现：

步骤一：结构与索引克隆：

利用 LIKE 语法完整继承原表的所有物理结构和索引树定义：

sql

CREATE TABLE employees_backup LIKE employees;

步骤二：全量数据注入：

利用 INSERT INTO ... SELECT 将源表数据完整检索并批量灌入新表：

sql

INSERT INTO employees_backup
SELECT * FROM employees;

~~执行流程~~：

在 MySQL 存储引擎底层，高保真复制的处理过程如下：

元数据克隆与物理建表：读取源表的数据字典，为新表分配表空间，完整构建主键与二级索引树定义，重置自增计数器。
源表数据扫描与加锁：执行 SELECT 顺序读取源表数据（在默认 REPEATABLE READ 隔离级别下，源表记录会被施加共享读锁）。
聚簇索引写入与校验：数据行按主键顺序批量写入新表的聚簇索引，并进行非空及唯一性约束校验。
二级索引同步维护：向新表的各个二级索引 B+ 树写入索引项（在内存不足时利用 Change Buffer 延迟合并）。
事务持久化提交：写入 Redo Log 与 Binlog，完成事务提交流程。

~~方案对比~~：

对比维度	CTAS 语法复制	高保真两步复制（LIKE + INSERT）	Clone 插件物理克隆（MySQL 8.0.17+）
表结构与字段	复制	复制	复制
主键与二级索引	全部丢失	完整保留	完整保留
自增属性（AUTO_INCREMENT）	丢失	保留（初始值需校准）	完整保留
索引构建开销	无额外索引维护	插入时实时维护索引树	物理页面直接拷贝，性能极高
适用数据量级	小数据量临时表	中小型表全量复刻	千万级以上超大表快速克隆

~~优化建议~~：

大表分批插入：单次写入百万级数据会导致源表被长时间加锁、事务过大引发从库延迟。建议按主键范围分批迁移：
sql
```
INSERT INTO employees_backup SELECT * FROM employees WHERE id BETWEEN 1 AND 50000;
```
1
自增计数器校准：通过 LIKE 新建的空表，其 AUTO_INCREMENT 会重置为默认值。数据导入完毕后，应手动校准自增起始值：
sql
```
ALTER TABLE employees_backup AUTO_INCREMENT = 100050;
```
1
先插数据后建索引（可选）：如果源表包含大量二级索引且数据庞大，可以先通过 LIKE 复制表结构后临时删除二级索引，待全量数据写入完成后再统一执行 ALTER TABLE ADD INDEX，能显著减少 B+ 树页分裂带来的 I/O 开销。

表的去重

在 MySQL DQL 中，~~表的去重~~指在数据检索阶段剔除结果集中完全重复或在特定业务维度下重复的数据行。

根据去重粒度与保留规则，主要有关键字过滤、分组聚合、窗口函数与关联子查询等多种~~实现方案~~。

方案1：DISTINCT 关键字

~~DISTINCT~~ 是最直接的去重语法，用于消除结果集中完全相同的重复行。

单字段去重：返回该字段所有不重复的值。
多字段联合去重：作用于 SELECT 后的所有列，仅当多列组合的值完全相同时才会被去重。

聚合去重：配合聚合函数统计唯一值数量（如 COUNT(DISTINCT col)）。

sql

-- 1. 单字段去重
SELECT DISTINCT dept_id FROM employees;

-- 2. 多字段联合去重（部门 + 职位均相同才去重）
SELECT DISTINCT dept_id, job_title FROM employees;

-- 3. 统计不重复的部门总数
SELECT COUNT(DISTINCT dept_id) AS unique_depts FROM employees;

方案2：GROUP BY 分组去重

当需要在去重的同时进行聚合计算，或提取每个分组中的代表性指标时，使用 ~~GROUP BY~~：

sql

-- 提取每个部门的 ID 以及该部门的最高薪资（代表性指标）
SELECT dept_id, MAX(salary) AS max_salary
  FROM employees
  GROUP BY dept_id;

方案3：窗口函数去重

当需要根据特定业务规则（如“保留最新下单的记录”或“保留金额最大的一行”）提取完整的单行明细时，使用 ~~ROW_NUMBER()~~ 是标准做法：

sql

-- 按照用户分组，仅保留每个用户最新创建的一笔订单明细
WITH ranked_orders AS (
  SELECT
    order_id,
    user_id,
    amount,
    create_time,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY create_time DESC) AS rn
    FROM orders
)
SELECT order_id, user_id, amount, create_time
  FROM ranked_orders
  WHERE rn = 1;

解释：

获取每个用户最新创建的一笔订单数据（即典型的“分组取最新 / 分组 Top 1 去重”场景）。

WITH ranked_orders AS (...)（CTE 公用表表达式）
定义一个名为 ranked_orders 的临时结果集，方便后续主查询引用与过滤。
ROW_NUMBER() OVER (...) AS rn（窗口函数排序编号）
- PARTITION BY user_id：按 user_id 分组（为每个用户划分独立的数据分区）。
- ORDER BY create_time DESC：在每个用户的分区内部，按下单时间降序（从新到旧）排序。
- ROW_NUMBER()：为分区内的行依次分配从 1 开始的递增连续行号。因此，每个用户最新的一笔订单，其 rn 必定为 1。
WHERE rn = 1（主查询过滤）
从临时表中只筛选出序号为 1 的记录，过滤掉用户的所有历史旧订单，最终输出每个用户唯一且最新的一条记录。

方案4：子查询 / 极值关联去重

在 MySQL 5.7 等不支持窗口函数的版本中，可通过关联分组极值主键实现明细去重：

sql

-- 按 email 维度去重，保留每组主键 id 最小的完整行
SELECT e.*
  FROM employees e
  JOIN (
    SELECT MIN(id) AS min_id
    FROM employees
    GROUP BY email
  ) t ON e.id = t.min_id;

底层执行机制

MySQL 查询引擎在底层执行去重操作时，通常经历以下处理机制：

索引覆盖扫描（Loose / Tight Index Scan）：
- 若去重字段建立了有效索引，优化器直接利用 B+ 树索引的有序性跳跃式读取唯一键值。
- 无需额外排序或构建临时表，执行效率最高（执行计划显示 Using index）。
内存临时表与哈希判定（Memory Table）：
- 无索引可用时，MySQL 会在内存中开辟临时表，逐行计算哈希值或构建临时唯一索引进行去重过滤。
磁盘溢出与文件排序（Filesort / Disk Table）：
- 若去重的数据集体积超过 tmp_table_size 和 max_heap_table_size 上限，内存临时表会转化为磁盘临时表，借助磁盘归并排序（Filesort）完成去重，产生显著的 I/O 开销。

方案特性对比

去重方式	语法复杂度	完整字段保留能力	聚合计算支持	适用场景
`DISTINCT`	低	差（只能保留去重参与列）	仅限 `COUNT(DISTINCT)`	快速提取单列或多列唯一值清单
`GROUP BY`	中	需配合聚合函数（受 SQL 模式限制）	强（支持各类聚合）	去重统计与分类指标汇总
窗口函数（`ROW_NUMBER`）	中	优（可保留全表任意字段明细）	强（支持组内统计）	按优先级/时间取组内 Top 1 明细
极值子查询	较繁琐	良好（通过主键回表拉取明细）	中	旧版本 MySQL 提取唯一完整记录

物理数据清洗

若需将表中的重复历史数据进行物理删除，仅保留一条基准记录，可将 DQL 与 DML 连接结合：

sql

-- 针对 email 重复的数据，仅保留主键 id 最小的一条，删除其余冗余行
DELETE e1
  FROM employees e1
  JOIN employees e2
    ON e1.email = e2.email AND e1.id > e2.id;

注意事项

DISTINCT 位置规则：DISTINCT 关键字必须紧跟在 SELECT 之后，不能放置在某一特定字段中间（如 SELECT id, DISTINCT name 为非法语法）。
NULL 值合并规则：在 DISTINCT 和 GROUP BY 处理中，MySQL 将所有 NULL 值视为相同的值，去重后结果集中仅保留一个 NULL。
联合索引优化：对于高频的大表去重查询，应为去重字段（或 PARTITION BY 字段）建立联合索引，消除临时表与文件排序开销。

表的集合操作

在 MySQL DQL 中，集合操作（Set Operations）用于将两个或多个 SELECT 查询的结果集按纵向（行方向）合并为一个单一的结果集。与多表联查（横向扩展列）不同，集合操作实现的是数学意义上的集合运算（并集、交集与差集）。

基本规则

参与集合运算的各个子查询必须满足以下先决条件：

列数严格一致：每个 SELECT 语句查询列表中的字段数量必须相同。
数据类型兼容：各个 SELECT 对应位置的字段必须具有兼容的数据类型（或支持隐式类型转换）。
列名由首句决定：最终输出结果集的字段名与别名，由第一个 SELECT 语句声明的列名确定。

并集运算

UNION

UNION 会将多个查询结果合并为一个结果集，并自动去除重复记录：

sql

-- 查询拥有员工或客户的所有城市列表（自动去重）
SELECT city FROM employees
UNION
SELECT city FROM customers;

UNION ALL

UNION ALL 将所有查询结果直接拼接，全量保留重复记录：

sql

-- 统计所有员工与客户的城市全量数据（保留重复行，性能最高）
SELECT city FROM employees
UNION ALL
SELECT city FROM customers;

交集运算

MySQL 8.0.31 及以上版本正式支持原生交集运算符：

INTERSECT

返回同时存在于所有 SELECT 查询结果中的共同记录，并去除重复行：

sql

-- 查询既是员工所在城市、又是客户所在城市的交集城市
SELECT city FROM employees
INTERSECT
SELECT city FROM customers;

INTERSECT ALL

保留两表中共同记录的多重匹配行（若表 A 出现 3 次，表 B 出现 2 次，则结果保留 2 次）。

旧版本替代方案

在 MySQL 8.0.31 之前的版本中，通过 INNER JOIN 或 EXISTS 模拟交集：

sql

SELECT DISTINCT e.city
  FROM employees e
  INNER JOIN customers c ON e.city = c.city;

差集运算

MySQL 8.0.31 及以上版本正式支持原生差集运算符（功能等价于 Oracle 的 MINUS）：

EXCEPT

返回存在于第一个查询结果中、但不存在于后续查询结果中的记录，并去除重复行：

sql

-- 查询仅有员工、但没有任何客户的城市列表
SELECT city FROM employees
EXCEPT
SELECT city FROM customers;

EXCEPT ALL

计算多重集的差集（若表 A 出现 3 次，表 B 出现 1 次，则结果保留 2 次）。

旧版本替代方案

在不支持 EXCEPT 的旧版本中，通过 LEFT JOIN ... IS NULL 或 NOT EXISTS 模拟差集：

sql

SELECT DISTINCT e.city
  FROM employees e
  LEFT JOIN customers c ON e.city = c.city
  WHERE c.city IS NULL;

排序与分页

集合操作中的 ORDER BY 和 LIMIT 存在局部与全局两种作用域：

全局排序与分页

放置在整个集合语句的最后，对合并后的最终结果集整体生效：

sql

SELECT id, name, salary FROM dev_employees
UNION ALL
SELECT id, name, salary FROM test_employees
ORDER BY salary DESC
LIMIT 10;

分支局部排序与分页

若需对各个独立的子查询单独进行排序或限制，各个子查询必须用圆括号包裹：

sql

(SELECT id, name, salary FROM dev_employees ORDER BY salary DESC LIMIT 5)
UNION ALL
(SELECT id, name, salary FROM test_employees ORDER BY salary DESC LIMIT 5)
ORDER BY salary DESC;

执行机制

MySQL 查询引擎处理集合操作的底层步骤如下：

分支并行/顺序执行：独立执行参与集合运算的各个 SELECT 子查询，检索并过滤数据。
汇聚中间数据：
- UNION ALL 模式：采用流式传输，直接将各分支的数据行追加至最终结果集，无需构建临时表。
- UNION / INTERSECT / EXCEPT 模式：在内存中创建临时表（Memory Table），使用哈希表或唯一索引记录行哈希值。
执行集合筛选：根据运算符规则（去重、匹配判断或差集剔除）保留满足条件的数据行。
全局后置处理：若语句末尾声明了全局 ORDER BY 或 LIMIT，对最终中间结果集执行文件排序并截取数据输出。

方案对比

集合运算符	去重机制	MySQL 原生支持版本	内存开销	适用场景
`UNION`	自动去重	全版本支持	较高（需建临时表去重）	多源数据合并且需排除重复项
`UNION ALL`	保留重复项	全版本支持	极低（流式追加输出）	多源数据汇总、性能敏感型大表归并
`INTERSECT`	自动去重	MySQL 8.0.31+	较高（需哈希/临时表比对）	求解多个数据集的共有交集
`EXCEPT`	自动去重	MySQL 8.0.31+	较高（需哈希/临时表排他）	求解数据集之间的单向排他差集

S09-03 MySQL-高级SQL查询 ​

DQL ​

基础查询 ​

语法结构 ​

执行顺序 ​

WHERE ​

GROUP BY ​

ORDER BY ​

LIMIT ​

多表联查 ​

连接本质 ​

条件过滤 ​

底层算法 ​

优化准则 ​

内连接 INNER JOIN ​

左外连接 LEFT JOIN ​

右外连接 RIGHT JOIN ​

全外连接 FULL JOIN ​

自连接 SELF JOIN ​

交叉连接 CROSS JOIN ​

子查询 ​

出现位置 ​

执行流程 ​

常见陷阱与优化 ​

结果分类 ​

标量子查询 ​

列子查询 ​

行子查询 ​

表子查询 ​

相关性分类 ​

不相关子查询 ​

相关子查询 ​

表的复制 ​

执行流程 ​

方案对比 ​

注意事项 ​

方案1：结构与数据复制 ​

方案2：仅复制表结构 ​

方案3：复制数据到已有表 ​

方案4：完整高保真复制 ​

表的去重 ​

方案1：DISTINCT 关键字 ​

方案2：GROUP BY 分组去重 ​

方案3：窗口函数去重 ​

方案4：子查询 / 极值关联去重 ​

底层执行机制 ​

方案特性对比 ​

物理数据清洗 ​

注意事项 ​

表的集合操作 ​

基本规则 ​

并集运算 ​

UNION ​

UNION ALL ​

交集运算 ​

INTERSECT ​

INTERSECT ALL ​

旧版本替代方案 ​

差集运算 ​

EXCEPT ​

EXCEPT ALL ​

旧版本替代方案 ​

排序与分页 ​

全局排序与分页 ​

分支局部排序与分页 ​

执行机制 ​

方案对比 ​

S09-03 MySQL-高级SQL查询

DQL

基础查询

语法结构

执行顺序

WHERE

GROUP BY

ORDER BY

LIMIT

多表联查

连接本质

条件过滤

底层算法

优化准则

内连接 INNER JOIN

左外连接 LEFT JOIN

右外连接 RIGHT JOIN

全外连接 FULL JOIN

自连接 SELF JOIN

交叉连接 CROSS JOIN

子查询

出现位置

执行流程

常见陷阱与优化

结果分类

标量子查询

列子查询

行子查询

表子查询

相关性分类

不相关子查询

相关子查询

表的复制

执行流程

方案对比

注意事项

方案1：结构与数据复制

方案2：仅复制表结构

方案3：复制数据到已有表

方案4：完整高保真复制

表的去重

方案1：DISTINCT 关键字

方案2：GROUP BY 分组去重

方案3：窗口函数去重

方案4：子查询 / 极值关联去重

底层执行机制

方案特性对比

物理数据清洗

注意事项

表的集合操作

基本规则

并集运算

UNION

UNION ALL

交集运算

INTERSECT

INTERSECT ALL

旧版本替代方案

差集运算

EXCEPT

EXCEPT ALL

旧版本替代方案

排序与分页

全局排序与分页

分支局部排序与分页

执行机制

方案对比